﻿CREATE TABLE [dbo].[tblProduct] (
	[ProductID]            INT            IDENTITY (1, 1) NOT NULL,
	[ProductGroupID]       INT            NOT NULL,
	[OwnerID]              INT            NOT NULL,
	[ReorderProductID]     INT            NULL,
	[IsAProductID]         INT            NULL,
	[ItemCode]             VARCHAR (30)   NOT NULL,
	[ShortName]            NVARCHAR (20)  NOT NULL,
	[LongName]             NVARCHAR (100) NULL,
	[Description]          NVARCHAR (255) NULL,
	[InvoiceDesc]          NVARCHAR (255) NULL,
	[WebDescription]       NVARCHAR (255) NULL,
	[ActiveItem]           BIT            NOT NULL,
	[InventoryItem]        BIT            NOT NULL,
	[SalableItem]          BIT            NOT NULL,
	[AutoBreakdown]        BIT            NOT NULL, -- Automatically break down kit to make components available.
	[AutoBuild]			bit	not null default 0, -- Automatically take inventory from components to build kit
	[TrackIndividual]	bit	not null default 0, -- Track each individual item seperately as it enters and leavs the warehouse
	[MaxPerHousehold]      INT            NULL,
	[PreferredCarrierCode] int       NULL,
	[RequiredCarrierCode] int       NULL,
	[UnitCost]				money null,
	[Color]                NCHAR (20)     NULL,
	[Size]                 NCHAR (20)     NULL,
	[Weight]               DECIMAL (6, 2) NULL,
	[Oversize]             BIT            NULL,
	[UserDefined1]         NVARCHAR (50)  NULL,
	[UserDefined2]         NVARCHAR (50)  NULL,
	[UserDefined3]         NVARCHAR (10)  NULL,
	[UserDefined4]         NVARCHAR (10)  NULL,
	[UserDefined5]         INT            NULL,
	[UserDefined6]         INT            NULL,
	[UserDefined7]         SMALLDATETIME  NULL,
	[UserDefined8]         BIT            NULL,
	[UserDefined9]         BIT            NULL
);
GO
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Defines the product and it''s information', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Unique id', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'ProductID';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'The itemcode for reorder items', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'ReorderProductID';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'The item code', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'ItemCode';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'The product description', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'Description';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'The description of the item in the invoice', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'InvoiceDesc';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Whether this is a floor item (1) or not (0)', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'InventoryItem';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Whether this is a customer service item (1) or not (0).', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'SalableItem';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'The maximum number that can be sold to one household.  If there is no restriction
then the value is null.', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'MaxPerHousehold';
GO;
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'The preferred mail carrier for delivery.  See codeCarrier.', 
	@level0type = N'SCHEMA', @level0name = N'dbo',
	@level1type = N'TABLE', @level1name = N'tblProduct',
	@level2type = N'COLUMN', @level2name = N'PreferredCarrierCode';

